package com.immoc.jdbc.sample;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.immoc.jdbc.common.DbUtils;
import com.immoc.jdbc.hrapp.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

public class DbutilsSample {
    private static void query(){
        Properties properties = new Properties();
        String pFile = DbutilsSample.class.getResource("/druid-config.properties").getPath();
        try {
            pFile = URLDecoder.decode(pFile,"UTF-8");
            properties.load(new FileInputStream(pFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            QueryRunner qr = new QueryRunner(dataSource);
//            包装成 指定实体类 的列表
            List<Employee> list = qr.query("select * from employee limit ?,10", new BeanListHandler<>(Employee.class), new Object[]{10});
            for(Employee emp:list){
                System.out.println(emp.getEname());
            }
            // 不需要关闭连接因为 dbuitls会自动关闭
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
    private static void update(){
        Properties properties = new Properties();
        String pFile = DbutilsSample.class.getResource("/druid-config.properties").getPath();
        Connection conn = null;
        try {
            pFile = URLDecoder.decode(pFile,"UTF-8");
            properties.load(new FileInputStream(pFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            // 获取到数据库链接
            conn = dataSource.getConnection();
//         开启事务
            conn.setAutoCommit(false);
            String sql1 = "update employee set salary=salary+1000 where eno=?";
            String sql2 = "update employee set salary=salary-1000 where eno=?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn,sql1,new Object[]{0});
            qr.update(conn,sql2,new Object[]{1});
            conn.commit();

        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            try {
                if(conn!=null && !conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }finally {
            try {
                if(conn!=null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    public static void main(String[] args) {
        update();
    }
}
